{"text_format":"1","workbook":"xlsx","n_sheets":8,"sheets":["Cover sheet","Main sheet","Framework","Viability assessment","Restructuring","Sales to third party","Collateral enforcement","Insolvency proceeding"],"not_rendered":{"charts":0,"images":2,"macros":1,"external_links":0,"defined_names":0,"comments":2,"pivot_tables":0},"unopened":[],"bounds":{"document_bytes":16777216,"sheet_cells":100000,"rows_first":2000,"rows_last":500,"rows_between":2000,"halvings":0,"cell_chars":16384,"cell_head_chars":4096},"cells_cut":0,"cells_raw":0,"read":"whole","date_system":"1900"}

{"sheet":"Cover sheet","index":0,"n_rows":14,"n_cols":9,"rows_kept":14,"cut":false,"hidden":false,"merged":[[13,8,14,10]]}
[]
[]
[]
[]
[]
[]
[]
[null,"Course on Nonperforming Loan Resolution",null,null,null,null,null,null,"NPL Resolution Case Study #1"]
[null,"2021-11-26"]
[]
[null,"Instructor",null,"Karlis Bauze"]
[null,null,null,"Senior Financial Sector Specialist"]
[]
[null,null,null,null,null,null,null,null,"Please enable macros to operate this spreadsheet."]

{"sheet":"Main sheet","index":1,"n_rows":60,"n_cols":13,"rows_kept":60,"cut":false,"hidden":false,"merged":[[56,9,56,10],[57,9,57,10],[21,5,21,6],[21,9,21,10],[53,1,54,2],[16,4,17,4],[1,1,2,2],[20,1,21,2],[23,1,24,2],[47,4,48,4],[40,4,41,4],[27,4,28,4],[34,4,35,4],[6,4,7,4],[27,2,28,3],[40,2,41,3],[47,2,48,3],[34,2,35,3],[6,2,7,3]]}
[]
[null,"Background"]
[null,null,null,null,"Please read the background information in this section, then proceed to \"Viability assessment\" below."]
[]
[null,null,"Loan to be resolved"]
[]
[null,null,"Values in local currency",null,"NPV","31.12.16","31.12.17","31.12.18","31.12.19","31.12.20","31.12.21"]
[null,null,null,null,null,"t = 0","t + 1","t + 2","t + 3","t + 4","t + 5"]
[null,null,"Principal outstanding",null,{"$f":"NPV($F$14,G9:K9)+F9","v":9788709},9788709]
[null,null,"Interest outstanding",null,{"$f":"NPV($F$14,G10:K10)+F10","v":0},0]
[]
[null,null,"Parameters"]
[]
[null,null,"Interest rate over time",null,null,0.1,null,"Years to enforce collateral (insolvency)",null,null,3]
[null,null,"Years to enforce collateral",null,null,3,null,"Years to receive liquidation proceeds",null,null,5]
[]
[null,null,null,null,null,"31.12.16","31.12.17","31.12.18","31.12.19","31.12.20","31.12.21"]
[null,null,null,null,null,"t = 0","t + 1","t + 2","t + 3","t + 4","t + 5"]
[null,null,"Exchange rate: local currency to USD",null,null,27.19,27.75,28.31,28.88,29.44,30]
[]
[null,"Viability assessment",null,null,null,"Click on \"Start\" on the button to the left and determine:"]
[null,null,null,null,null,"Is the company viable?",null,null,null,{"$f":"IF(ISBLANK('Viability assessment'!H3), \" \", 'Viability assessment'!H3)","v":"Yes"}]
[]
[null,"Workout options",null,null,null,"Once viability is assessed, click on the \"Update\" button to expand the workout options."]
[null,null,null,null,null,"Click on \"Start\" to explore details of individual options."]
[]
[]
[null,null,"Restructuring",null,"NPV","31.12.16","31.12.17","31.12.18","31.12.19","31.12.20","31.12.21",null,"NPV"]
[null,null,null,null,null,"t = 0","t + 1","t + 2","t + 3","t + 4","t + 5"]
[null,null,"Principal"]
[null,null,"Interest"]
[null,null,"Sum"]
[]
[]
[null,null,"Sales to third party",null,"NPV","31.12.16","31.12.17","31.12.18","31.12.19","31.12.20","31.12.21",null,"NPV"]
[null,null,null,null,null,"t = 0","t + 1","t + 2","t + 3","t + 4","t + 5"]
[null,null,"Proposal 1"]
[null,null,"Proposal 2"]
[]
[]
[null,null,"Collateral enforcement",null,"NPV","31.12.16","31.12.17","31.12.18","31.12.19","31.12.20","31.12.21",null,"NPV"]
[null,null,null,null,null,"t = 0","t + 1","t + 2","t + 3","t + 4","t + 5"]
[null,null,"Recovery"]
[null,null,"Expenses"]
[null,null,"Sum"]
[]
[]
[null,null,"Insolvency proceeding",null,"NPV","31.12.16","31.12.17","31.12.18","31.12.19","31.12.20","31.12.21",null,"NPV"]
[null,null,null,null,null,"t = 0","t + 1","t + 2","t + 3","t + 4","t + 5"]
[null,null,"Recovery"]
[null,null,"Expenses"]
[null,null,"Sum"]
[]
["Restructuring","Conclusion"]
["Sales to third party",null,null,null,"Edit the interest rate parameter in cell E14 and use results above to answer the following questions:"]
["Collateral enforcement"]
["Insolvency proceeding",null,"The best workout option, assuming a constant interest rate of 10%, is:"]
[null,null,"                                           … assuming a constant interest rate of 20%, is:"]
[]
[null,null,null,null,"What would be the reasons behind?"]

{"sheet":"Framework","index":2,"n_rows":0,"n_cols":0,"rows_kept":0,"cut":false,"hidden":false,"merged":[]}

{"sheet":"Viability assessment","index":3,"n_rows":39,"n_cols":15,"rows_kept":39,"cut":false,"hidden":false,"merged":[[1,1,2,2]]}
[]
["Yes","Viability  assessment",null,null,"Analyze material below, and click on the green cell's dropdown menu to determine:"]
["No",null,null,null,"Is the company viable?",null,null,"Yes"]
[]
[null,"Background",null,null,"Once done, click \"Finish\" to return."]
[]
[null,"In December 2012 the bank lent to the company USD 360,000 to fund the modernization and working capital increase."]
[null,"The loan is repayable in USD 10,000 instalments over three years starting from January 2015."]
[null,"Monthly interest payments are based on 10 percent annual interest rate."]
[null,"The loan is secured by a mortgage of real estate, pledge of equipment, and furniture with a total estimated value of local currency 2,000,000 at loan origination. "]
[null,"Additionally, the controlling shareholder provided personal guaranty for the loan. "]
[]
[null,"In December 2014, the Bank postponed the start of principal repayment from January 2015 to January 2017 as part of a general bank-wide program of debt restructuring in response to macroeconomic deterioration. "]
[]
[null,"Financial statement"]
[]
[null,null,null,null,2014,2015,"2016 (E)","Mid-term targets"]
[null,"Sales",null,null,5055373,5497489,5914613,7044398]
[null,"EBITDA",null,null,919756,966316,1617809,1767436]
[null,"EBIT",null,null,832755,885695,1540368,1631084]
[null,"Net earnings",null,null,-2295960,-2467443,-588442,{"$f":"(G21-F21)/2","v":939500.5}]
[null,"Key financial ratios (in percentages)",null,null,null,null,null,null,null,"Reference benchmarks*",null,null,"Good","Moderate","Bad"]
[null,"    Compound annual growth rate of revenue",null,null,{"$f":"(G23-F23)/2","v":-0.57896533175900711},{"$f":"(F18/E18-1) * 100","v":8.7454674462200899},{"$f":"(G18/F18-1) * 100","v":7.5875367827020757},{"$f":"((H18/G18-1) * 100)^0.5","v":4.3705363378798969},null,"    Compound annual growth rate of revenue",null,null,"> 5%","-5% to 5%","< -5%"]
[null,"    Net debt / EBITDA",null,null,5.7,8.9,6.1,1.9,null,"    Net debt / EBITDA ratio",null,null,"< 1","1 to 5","> 5"]
[null,"    Debt service coverage ratio",null,null,2.1,1.3,1.7,5.2,null,"    Debt service coverage ratio",null,null,"> 1.5","1 to 1.5","< 1"]
[null,null,null,null,null,null,null,null,null,"*: Values adapted from three studies by Deloitte, KPMG, and PwC in Balkan countries."]
[null,"Additional Information"]
[]
[null,"In mid-2014, the company suffered from deep local currency depreciation, while the loan was denominated in USD."]
[null,"The company faced difficulties in raw material supply as well as decreased prices and sales disruption in foreign markets."]
[null,"Sales and profits continued to be below forecasted targets throughout 2014-16."]
[null,"The company was added to the watch list in 2015 based on its weak financial position mainly due to foreign currency loan revaluation and EBITDA much below the business plan targets."]
[]
[null,"The financial projections assumed modest increase in sales for the next 5 years driven mainly by volume increase. "]
[null,"Forecast prices for steel products were in line with the bank’s own internal forecasts."]
[null,"EBIT and EBITDA margins were expected to improve slightly with increased capacity utilization."]
[null,"Although the company would have negative equity resulting from accumulated foreign currency losses, it would turn to positive net earnings as soon as local currency depreciation ceased."]
[]
[null,"Indicate your assessment on the firm's viability in the green cell at the top of the page, then click on \"Finish\" to return to the main page."]

{"sheet":"Restructuring","index":4,"n_rows":37,"n_cols":10,"rows_kept":37,"cut":false,"hidden":false,"merged":[[1,1,2,2],[21,2,22,3]]}
[]
[null,"Restructuring",null,null,"Review the description below on the restructuring process."]
[null,null,null,null,"Once done, click on the \"Finish\" button to return to main page."]
[]
[null,"Background"]
[]
[null,"Loan conversion into local currency was not required because half of the sales were from exporting, generating foreign currency revenue."]
[null,"The guidelines recommended a tenor not to exceed five years, EBITDA of at least 110 percent of principal and interest repayments, a maximum rate of 8 percent. "]
[null,"With average annual cash flow of almost USD 62,000 (assuming further 2 percent annual local currency depreciation), the most the company could afford to pay on an annual basis was USD 56,000."]
[null,"This amount was insufficient to meet debt service requirements that, according to the signed loan agreement, should amount on average to about USD 144,000 for the next 3 years. "]
[]
[null,"EMP would not be able to repay its outstanding debt in full over a five-year period as the principal payments alone would amount to USD 72,000 per year on average. "]
[null,"The loan officer reduced the debt in USD 5,000 increments until he reached USD 225,000."]
[null,"Principal payments together with interest at 8 percent would total on average USD 55,277 below maximum target. "]
[null,"At the same time, the loan officer was unwilling to recommend that the Bank forgive the remaining USD 135,000 balance on the loan at this time. "]
[null,"He therefore recommended that the existing debt to be split into two loans, both secured by a mortgage on the land and building, as well as pledges over furniture, fixtures, and equipment. "]
[]
[null,"The first loan, or the “A” loan, would be in the amount of USD 225,000 and would be payable in 60 instalments with a first instalment of USD 2,214 and each next instalment by USD 52 higher, plus 8 percent interest on outstanding debt."]
[null,"The remaining balance of USD 135,000, would be placed on the “B” note, at 0 percent interest and a bullet maturity of five years. "]
[null,"To ensure continued cooperation by the company, the Bank would agree in a new master restructuring agreement that the B note would be forgiven if the A note was repaid in accordance with its terms. "]
[]
[null,null,"Restructuring",null,"31.12.16","31.12.17","31.12.18","31.12.19","31.12.20","31.12.21"]
[null,null,null,null,"t = 0","t + 1","t + 2","t + 3","t + 4","t + 5"]
[null,null,"\"A\" note"]
[null,null,"    Principal",null,0,824142,1051254,1286794,1530762,1783157]
[null,null,"    Interest",null,0,461860,396373,310879,204368,75833]
[null,null,"\"B\" note"]
[null,null,"    Principal (conditional)",null,0,0,0,0,0,{"$f":"135000*27.2","v":3672000}]
[]
[null,"Conditions"]
[null,null,"Covenant restricting dividends and other withdrawals;"]
[null,null,"Monthly cash budget together with quarterly financial statements;"]
[null,null,"Maintenance of EBITDA debt service coverage at all times of at least 1.1;"]
[null,null,"EBITDA above USD 55,000 be used to the “A” note until it is repaid;"]
[null,null,"“B” note was not to be used in the calculation of the financial leverage covenants."]
[]
[null,"Now, click on \"Finish\" at top of the spreadsheet to return to the main page."]

{"sheet":"Sales to third party","index":5,"n_rows":12,"n_cols":10,"rows_kept":12,"cut":false,"hidden":false,"merged":[[1,1,2,2],[8,2,9,3]]}
[]
[null,"Sales to third party"]
[null,null,null,null,"Consider the scenario below, then return to the main page:"]
[]
[null,"Background"]
[]
[null,"The following offers have been received:"]
[]
[null,null,"Offers",null,"31.12.16","31.12.17","31.12.18","31.12.19","31.12.20","31.12.21"]
[null,null,null,null,"t = 0","t + 1","t + 2","t + 3","t + 4","t + 5"]
[null,null,"Proposal 1",null,3750000,1100000,0,0,0,0]
[null,null,"Proposal 2",null,1350000,1800000,2250000,0,0,0]

{"sheet":"Collateral enforcement","index":6,"n_rows":44,"n_cols":11,"rows_kept":44,"cut":false,"hidden":false,"merged":[[34,10,35,10],[1,1,2,2],[8,6,8,7],[8,4,8,5],[22,4,22,7],[34,2,35,3]]}
[]
[null,"Collateral enforcement"]
[null,null,null,null,"Consider the information below, then return to the main page:"]
[]
[null,null,"Collateral values"]
[]
[null,null,"The collaterals have been reappraised. They will be liquidated for values below if enforced:"]
[]
[null,null,null,null,"Appraisal values",null,"Sales value at enforcement"]
[null,null,null,null,"Original","Updated","Value","% of appraisal value"]
[null,null,"Land plot",null,300000,500000,{"$f":"F11*H11","v":300000},0.6]
[null,null,"Building",null,1500000,2000000,{"$f":"F12*H12","v":1200000},0.6]
[null,null,"Equipment",null,150000,100000,{"$f":"F13*H13","v":45000},0.45]
[null,null,"Furniture",null,50000,20000,{"$f":"F14*H14","v":6000},0.3]
[null,null,"Total",null,{"$f":"SUM(E11:E14)","v":2000000},{"$f":"SUM(F11:F14)","v":2620000},{"$f":"SUM(G11:G14)","v":1551000}]
[]
[null,null,"The proceeds are expected to be received in:",null,null,null,{"$f":"'Main sheet'!F15","v":3},"years."]
[]
[null,null,"Collateral enforcement costs"]
[]
[null,null,"Additional costs are to be involved in enforcing the collaterals, over a span of 3 years."]
[]
[null,null,null,null,"Amount"]
[null,null,"Court fees (two levels)",null,82530,3.2,"% of claim or limit of 504,000"]
[null,null,"Bailiff initiation fee ",null,32000,2,"% of claim or limit of 32,000"]
[null,null,"Bailiff service fee",null,155100,10,"% of forced sale value"]
[null,null,"Auction platform fee",null,77550,5,"% of forced sale value"]
[null,null,"Other selling costs",null,15510,1,"% of forced sale value"]
[null,null,"Total",null,{"$f":"SUM(E24:E28)","v":362690}]
[]
[null,null,"The distribution of expenses is indicated as expected below:"]
[]
[null,null,"Expected outcome"]
[]
[null,null,"Collateral enforcement",null,"31.12.16","31.12.17","31.12.18","31.12.19","31.12.20","31.12.21","Total"]
[null,null,null,null,"t = 0","t + 1","t + 2","t + 3","t + 4","t + 5"]
[null,null,"Recovery",null,0,0,0,{"$f":"G15","v":1551000},0,0,{"$f":"SUM(F37:J37)","v":1551000}]
[null,null,"Expenses",null,-39300,-48400,-37170,-237820,0,0,{"$f":"E29","v":362690}]
[]
[null,null,"For additional background on collateral enforcement, refer to cross-country information at:"]
[null,null,"http://www.doingbusiness.org/en/data/exploretopics/enforcing-contracts"]
[]
[]
[null,"Now, click on \"Finish\" at top of the spreadsheet to return to the main page."]

{"sheet":"Insolvency proceeding","index":7,"n_rows":74,"n_cols":11,"rows_kept":74,"cut":false,"hidden":false,"merged":[[66,10,67,10],[1,1,2,2],[8,6,8,7],[8,4,8,5],[50,5,50,7],[66,2,67,3]]}
[]
[null,"Insolvency proceeding"]
[null,null,null,"                        Consider the information below, then return to the main page:"]
[]
[null,null,"Collateral values"]
[]
[null,null,"The collaterals have been reappraised. They will be liquidated for less under insolvency:"]
[]
[null,null,null,null,"Appraisal values",null,"Sales value at enforcement"]
[null,null,null,null,"Original","Updated","Value","% of appraisal value"]
[null,null,"Land plot",null,300000,500000,{"$f":"F11*H11","v":200000},0.4]
[null,null,"Building",null,1500000,2000000,{"$f":"F12*H12","v":800000},0.4]
[null,null,"Equipment",null,150000,100000,35000,0.35]
[null,null,"Furniture",null,50000,20000,4000,0.2]
[null,null,"Total",null,{"$f":"SUM(E11:E14)","v":2000000},{"$f":"SUM(F11:F14)","v":2620000},{"$f":"SUM(G11:G14)","v":1039000}]
[]
[null,null,"Proceeds from collateral sales are to be received in:",null,null,null,{"$f":"'Main sheet'!K14","v":3},"years."]
[]
[null,null,"Liquidation values of assets"]
[]
[null,null,"Balance sheet item",null,"Book value","Estimated recovery rate","Liquidation value"]
[null,null,"Cash",null,0,1,{"$f":"E22*F22","v":0}]
[null,null,"Accounts receivable"]
[null,null,"    0-30 days outstanding",null,2873178,0.6,{"$f":"E24*F24","v":1723906.8}]
[null,null,"    31-60 days outstanding",null,1676021,0.3,{"$f":"E25*F25","v":502806.3}]
[null,null,"    Over 60 days outstanding",null,239432,0.1,23943.200000000001]
[null,null,"Total accounts receivable",null,{"$f":"SUM(E24:E26)","v":4788631},null,{"$f":"SUM(G24:G26)","v":2250656.3000000003}]
[null,null,"Inventory"]
[null,null,"    Finished goods",null,222050,0.7,{"$f":"E29*F29","v":155435}]
[null,null,"    Work in progress",null,296066,0,0]
[null,null,"    Raw materials",null,222050,0.5,111025]
[null,null,"Total inventory",null,{"$f":"SUM(E29:E31)","v":740166},null,{"$f":"SUM(G29:G31)","v":266460}]
[null,null,"Other current assets"]
[null,null,"    Prepaid expenses",null,0,0.5,{"$f":"E34*F34","v":0}]
[null,null,"    Financial investments",null,367259,0.3,110177.7]
[null,null,"    Promissory notes",null,456716,0.3,137014.79999999999]
[null,null,"Total other current assets",null,{"$f":"SUM(E34:E36)","v":823975},null,{"$f":"SUM(G34:G36)","v":247192.5}]
[null,null,"Non-current assets"]
[null,null,"    Land plots",null,0,0.7,{"$f":"E39*F39","v":0}]
[null,null,"    Buildings",null,0,0.7,0]
[null,null,"    Equipment",null,0,0.5,0]
[null,null,"    Other items",null,204040,0.1,20404]
[null,null,"Total non-current assets",null,{"$f":"SUM(E39:E42)","v":204040},null,{"$f":"SUM(G39:G42)","v":20404}]
[null,null,"Total assets",null,{"$f":"SUM(E27,E32)","v":5528797},null,{"$f":"SUM(G27,G32,G43)","v":2537520.3000000003}]
[null,null,"Portion of recovery by the bank",null,null,0.81,{"$f":"G44*F45","v":2055391.4430000004}]
[]
[null,null,"Proceeds from asset liquidation are to be received in:",null,null,null,{"$f":"'Main sheet'!K15","v":5},"years."]
[]
[null,null,"Insolvency proceeding costs"]
[]
[null,null,null,null,"Amount","Explanation"]
[null,null,"Court fees (two levels)",null,308344,3.2,"% of claim or limit of 504,000"]
[null,null,"Insolvency case opening",null,16000,10,"times minimal living expenses"]
[null,null,"Statement of claims",null,6400,4,"times minimal living expenses"]
[null,null,"Bailiff initiation fee ",null,32000,2,"% of claim or limit of 32,000"]
[null,null,"Arbitrage manager pay",null,30924,2,"times minimal wage for 6 months"]
[null,null,"Liquidator pay",null,216467,2,"times minimal wage for 42 months"]
[null,null,"Sale organizer fee",null,154125,5,"% of forced sale value"]
[null,null,"Extra pay to liquidator / manager",null,76044,3,"% of net recovery value"]
[null,null,"Other selling costs",null,30825,1,"% of forced sale value"]
[null,null,"Total",null,{"$f":"SUM(E52:E60)","v":871129}]
[]
[null,null,"The distribution of expenses over time is indicated as expected below:"]
[]
[null,null,"Expected outcome"]
[]
[null,null,"Insolvency proceeding",null,"31.12.16","31.12.17","31.12.18","31.12.19","31.12.20","31.12.21","Total"]
[null,null,null,null,"t = 0","t + 1","t + 2","t + 3","t + 4","t + 5"]
[null,null,"Recovery",null,0,0,0,0,{"$f":"G15","v":1039000},{"$f":"G45","v":2055391.4430000004},{"$f":"SUM(F69:J69)","v":3094391.4430000004}]
[null,null,"Expenses",null,-146831,-215914,-65311,-72123,-149341,-221609,{"$f":"E61","v":871129}]
[]
[null,null,"http://www.doingbusiness.org/en/data/exploretopics/resolving-insolvency"]
[]
[null,"Now, click on \"Finish\" at top of the spreadsheet to return to the main page."]